<?php

/**
 * 
 * 导入导出，excel
 * @author 叶阳军
 */
class Excel {

    private static $head = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF','AG','AH','AI','AJ','AK');
    /**
     * 导出2007 excel
     * @param array $data  导出的数据 二维
     * @param array $field excel每类第一次的头例如：array('姓名','年龄');（一维数组）
     * @param string $title 文件标题
     * @param string $name 文件名
     */  
    public static function export($data, $field, $title, $name) {
        error_reporting(E_ALL); 
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
        date_default_timezone_set('Europe/London');
        if (PHP_SAPI == 'cli')
            die('This example should only be run from a Web Browser');
        $objPHPExcel = new PHPExcel();
      //  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
//        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                ->setLastModifiedBy("Maarten Balliauw")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file");

        // Add some data
        foreach ($field as $k => $v) {
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue(self::$head[$k].'1',$v);
        }
        // Miscellaneous glyphs, UTF-8
        $i = 2;
        $data_arr = array_values($data);
        foreach ($data_arr as $values) {
            $res = array_values($values);
            foreach ($res as $key => $vl) {
                /*if(strpos($vl, '0') === 0){   //处理第一个字符为0
                                $vl = "'" . $vl;
                            }*/
                
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue(self::$head[$key] . $i, $vl);
            }
            $i++;
        }
        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($title);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet = $objPHPExcel->getActiveSheet(); 
//        $objActSheet->getDefaultColumnDimension()->setAutoSize(true);//自动调节列宽
//        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(100);//设置默认列宽
//        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(30);//调节列宽

        return $objPHPExcel;
    }
    
    public static function do_export($objPHPExcel,$name){
           // Redirect output to a client’s web browser (Excel2007)
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
       // header("Content-Disposition: attachment;filename={$name}.xlsx");
        header("Content-Disposition: attachment;filename={$name}.xls");
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        
        $objWriter->save('php://output');
        exit;
    }
     
    

    public static function import($file) {
        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        $cacheSettings = array();
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
        $objPHPExcel = new PHPExcel();
        $objPHPExcel = PHPExcel_IOFactory::load($file); 
        return $data = $objPHPExcel->getSheet(0)->toArray();
    }

}